package net.huashitong.synthetical.newSupervision.dao;

import com.sirdc.modules.core.dao.GenericQuery;
import com.sirdc.modules.core.dao.StringDao;
import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import net.huashitong.synthetical.newSupervision.entity.TaskNew;
import net.huashitong.synthetical.newSupervision.entity.TaskSend;
import net.huashitong.synthetical.newSupervision.filter.TaskNewFilter;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: ling
 * Date: 2019/7/2
 * Time: 20:13
 * To change this template use File | Settings | File Templates.
 * Description:
 */
@Repository
public class TaskSendDao extends StringDao<TaskSend> {

    public List<TaskSend> queryByUserId(String userId) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(userId))
            query.append(" and userId=:userId").setParam("userId", userId);
        return query.listResult();
    }

    public List<TaskSend> queryByTaskId(String taskId) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(taskId)) {
            query.append(" and taskId=:taskId").setParam("taskId", taskId);
        }
        return query.listResult();
    }

    /**
     * 根据taskId批量删除
     *
     * @param taskIds
     */
    public void batchDeleteTaskIds(List<String> taskIds) {
        StringBuffer hql = (new StringBuffer("delete from TaskSend")).append(" obj where obj.taskId in(:taskIds)");
        Query query = this.getSession().createQuery(hql.toString());
        query.setParameterList("taskIds", taskIds);
        query.executeUpdate();
    }

    public List<TaskSend> queryByTaskIdAndType(String taskId, String type) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(taskId)) {
            query.append(" and taskId = :taskId").setParam("taskId", taskId);
        }
        query.append(" and type < :type").setParam("type", type);
        return query.listResult();
    }

    /**
     * 查询当前任务下指定状态下的任务
     * @param taskId
     * @param type
     * @return
     */
    public List<TaskSend> findByTaskIdAndType(String taskId, String type) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(taskId)) {
            query.append(" and taskId = :taskId").setParam("taskId", taskId);
        }
        query.append(" and type =:type").setParam("type", type);
        return query.listResult();
    }

    //查询逾期任务
    public List<TaskSend> findByTaskIdAndStatus(String taskId, String status) {
        GenericQuery query = createQuery("obj");
        if (StringUtils.isNotBlank(taskId)) {
            query.append(" and taskId = :taskId").setParam("taskId", taskId);
        }
        query.append(" and status = :status").setParam("status", status);
        return query.listResult();
    }


    /**
     * 查询单位任务
     * @param filter
     * @return
     */
    public List<TaskNew> queryFilter(TaskNewFilter filter) {
        StringBuilder sql = new StringBuilder();

        //将send表的主键别名为SYSId返回到表单中说sysid就是send表的主键
        sql.append("select s.sys_id sysId,n.task_name taskName,n.category_id categoryId,n.date_time dateTime,n.operator operator,n.file file,s.type type,s.status status,s.overdue_time overdueTime from " +
                " t_task_new as n, ( select sys_id,task_id ,status,type,overdue_time from t_task_send  where user_id = '" + filter.getUserId() + "') as s ");
        sql.append(" where 1=1 ");
        if (StringUtils.isNotBlank(filter.getTaskName())) {
            sql.append(" and n.task_name like '%" + filter.getTaskName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getCategoryId())) {
            sql.append(" and n.category_id = '" + filter.getCategoryId() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and s.type = '" + filter.getType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getLeader())) {
            sql.append(" and n.leader like '%" + filter.getLeader() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getOperator())) {
            sql.append(" and n.operator like '%" + filter.getOperator() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getDateTimeStart())) {
            sql.append(" and n.date_time >= '" + filter.getDateTimeStart() + "'");
        }
        if (StringUtils.isNotBlank(filter.getDateTimeEnd())) {
            sql.append(" and n.date_time <= '" + filter.getDateTimeEnd() + "'");
        }
        if (StringUtils.isNotBlank(filter.getStatus()))
            sql.append(" and s.status = '"+ filter.getStatus()+"'");

        sql.append(" and n.sys_id = s.task_id");
      //  sql.append(" order by s.sys_id ");

         // 设置查询总条数,用于分页
        int resultSize = this.count(filter);
        filter.getPaging().setRecords(resultSize);
        //设置排序
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("s.sys_id");
                filter.getPaging().setSortVal("desc");
            }
            sql.append(" order by "+filter.getPaging().getSortCol()+" "+filter.getPaging().getSortVal());
           }

        SQLQuery query = getSession().createSQLQuery(sql.toString());

        query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
        query.setMaxResults(filter.getPaging().getPageSize());
        query.setResultTransformer(Transformers.aliasToBean(TaskNew.class));
        List<TaskNew> list = query.list();
        return list;
    }

    public int count(TaskNewFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(s.sys_id) from " +
                " t_task_new as n, ( select sys_id,task_id ,type from t_task_send  where user_id = '" + filter.getUserId() + "') as s ");
        sql.append(" where 1=1 ");
        if (StringUtils.isNotBlank(filter.getTaskName())) {
            sql.append(" and n.task_name like '%" + filter.getTaskName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getCategoryId())) {
            sql.append(" and n.category_id = '" + filter.getCategoryId() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and n.type = '" + filter.getType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getLeader())) {
            sql.append(" and n.leader like '%" + filter.getLeader() + "%'");
        }
        sql.append(" and n.sys_id = s.task_id");

        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }
 /**
     * 查询领导批转的任务列表
     * @param filter
     * @return
     */
    public List<TaskNew> queryEndorseList(TaskNewFilter filter) {
        StringBuilder sql = new StringBuilder();

        //将send表的主键别名为SYSId返回到表单中说sysid就是send表的主键
        sql.append("select s.sys_id sysId,n.task_name taskName,n.category_id categoryId,n.date_time dateTime,n.operator operator,n.file file,s.type type,s.status status,s.overdue_time overdueTime from " +
                " t_task_new as n, ( select sys_id,task_id ,status,type,overdue_time from t_task_send  where person_id = '" + filter.getLeaderId() + "') as s ");
        sql.append(" where 1=1 ");
        if (StringUtils.isNotBlank(filter.getTaskName())) {
            sql.append(" and n.task_name like '%" + filter.getTaskName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getCategoryId())) {
            sql.append(" and n.category_id = '" + filter.getCategoryId() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and s.type = '" + filter.getType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getLeader())) {
            sql.append(" and n.leader like '%" + filter.getLeader() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getOperator())) {
            sql.append(" and n.operator like '%" + filter.getOperator() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getDateTimeStart())) {
            sql.append(" and n.date_time >= '" + filter.getDateTimeStart() + "'");
        }
        if (StringUtils.isNotBlank(filter.getDateTimeEnd())) {
            sql.append(" and n.date_time <= '" + filter.getDateTimeEnd() + "'");
        }
        if (StringUtils.isNotBlank(filter.getStatus()))
            sql.append(" and s.status = '"+ filter.getStatus()+"'");

        sql.append(" and n.sys_id = s.task_id");
      //  sql.append(" order by s.sys_id ");

         // 设置查询总条数,用于分页
        int resultSize = this.countEndorseList(filter);
        filter.getPaging().setRecords(resultSize);
        //设置排序
        if (ObjectUtils.isNotBlank(filter.getPaging())) {
            if (StringUtils.isBlank(filter.getPaging().getSortCol())) {
                filter.getPaging().setSortCol("sysId");
                filter.getPaging().setSortVal("desc");
            }
            sql.append(" order by "+filter.getPaging().getSortCol()+" "+filter.getPaging().getSortVal());
           }

        SQLQuery query = getSession().createSQLQuery(sql.toString());

        query.setFirstResult(filter.getPaging().getPageSize() * (filter.getPaging().getCurrentPage() - 1));
        query.setMaxResults(filter.getPaging().getPageSize());
        query.setResultTransformer(Transformers.aliasToBean(TaskNew.class));
        List<TaskNew> list = query.list();
        return list;
    }

    public int countEndorseList(TaskNewFilter filter) {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(s.sys_id) from " +
                " t_task_new as n, ( select sys_id,task_id ,type from t_task_send  where person_id = '" + filter.getLeaderId() + "') as s ");
        sql.append(" where 1=1 ");
        if (StringUtils.isNotBlank(filter.getTaskName())) {
            sql.append(" and n.task_name like '%" + filter.getTaskName() + "%'");
        }
        if (StringUtils.isNotBlank(filter.getCategoryId())) {
            sql.append(" and n.category_id = '" + filter.getCategoryId() + "'");
        }
        if (StringUtils.isNotBlank(filter.getType())) {
            sql.append(" and n.type = '" + filter.getType() + "'");
        }
        if (StringUtils.isNotBlank(filter.getLeader())) {
            sql.append(" and n.leader like '%" + filter.getLeader() + "%'");
        }
        sql.append(" and n.sys_id = s.task_id");

        SQLQuery query = getSession().createSQLQuery(sql.toString());
        return ((Number) query.uniqueResult()).intValue();
    }

}
